Solving complex query calculations used Nested Queries in SQL Server. The fact that Access allows you to easily construct calculated fields based on other calculated fields means that you can produce very complex calculations with great ease. Unfortunately SQL Server will not allow you to use this technique. If faced with a poorly performing Access Query that uses multiple layered queries, complex calculations and lots of IF logic in the queries, then you can be facing a bit of a nightmare to convert the SQL to a server-side query written in T-SQL.
For better understanding first you create a table in SQL Server and insert some values.
-- CREATE DATABASE
CREATE DATABASE OrgPvtLtd
GO
-- USE DATABASE
USE OrgPvtLtd
GO
-- CREATE TABLE
CREATE TABLE [ORDER DETAILS]
(
[ORDERID] INT IDENTITY(1,1) PRIMARY KEY,
PRODUCTID INT UNIQUE,
UNITPRICE MONEY NOT NULL,
QUANTITY INT NOT NULL
)
GO
-- INSERT VALUES
INSERT INTO [ORDER DETAILS](PRODUCTID,UNITPRICE,QUANTITY)
SELECT 100,265.00,2
UNION ALL
SELECT 101,362.00,3
UNION ALL
SELECT 102,635.35,1
UNION ALL
SELECT 103,562.00,5
UNION ALL
SELECT 104,32.00,4
GO
Problem Converting Calculations
SELECT [Order Details].UnitPrice, [Order Details].Quantity,
[UnitPrice]*[Quantity] AS LinePrice,
0.175*[LinePrice] AS VAT,
[LinePrice]+[VAT] AS TotalPrice
FROM [Order Details]
GO
Screen Shot
SQL Server does not allow calculated fields to refer to other calculations. You could try the following restating each calculation, but for complex calculations this can be very difficult.
-- ALternate solution
SELECT [Order Details].UnitPrice, [Order Details].Quantity,
[UnitPrice]*[Quantity] AS LinePrice,
0.175*[Quantity] *[UnitPrice] AS VAT,
[Quantity] *[UnitPrice]+ 0.175*[Quantity] *[UnitPrice] AS TotalPrice
FROM [Order Details]
GO
Screen Shot
Possible Solution
In Access it is common practice to design a query which uses another query, this process of layering queries on top of queries can be reproduced using views in SQL Server. Views have more limitations than Access queries but breaking a query down into a series of steps layered on top of each other is one possible solution. Although in a complex application this could involve making what already could be a sequence of views into an even greater layered sequence of views. So what other alternatives are there?
Another alternative is to use SQL Server functions, building functions which use other functions, and if you have time this could also provide a possible solution.
A third option would be to utilize joins, below is a partial solution to our problems, but the need here to specify the join criteria can again lead to unwanted complexity.
-- Complex solution
SELECT od.[OrderId], od.[ProductId],od.UnitPrice, od.Quantity,LinePrice, 0.175*[LinePrice] AS VAT
FROM [Order Details] AS od
INNER JOIN
(SELECT OrderId, ProductId,UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice
FROM [Order Details] ) AS JoinedQuery
ON od.[OrderId] = JoinedQuery.[OrderId]
AND od.[ProductId] = JoinedQuery.[ProductId]
GO
Screen Shot
Nested Queries
Whilst subqueries allow a query to be injected into another queries WHERE clause, nested queries allow a query to be injected into another queries FROM clause. This is an extremely elegant method for solving problems which require data to have complex calculations and summaries in a single step.The example below goes beyond the join solution, to give a full solution based on a three level query. The only syntactical issue is that at each level the query must be given a name using an AS clause.
-- Final Solution
SELECT *,[LinePrice]+[VAT] AS TotalPrice FROM
(SELECT *, 0.175*[LinePrice] AS VAT FROM
(SELECT UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice
FROM [Order Details]
) AS InnerQuery
) AS NextLevel
GO
Leave Comment